package com.aiflow.Action;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.struts2.ServletActionContext;

import util.ServletHelp;

import com.aiflow.db.ConnDB;

public class DictionaryAction {

	//查询字典
	public void findDictionary(){
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start")); 
		int limit = Integer.parseInt(req.getParameter("limit"));  
		String name = (String)req.getParameter("name");
		String para =  req.getParameter("para");  
		int end = start + limit; 
		int begin = start + 1;
		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String,String>> datalist = new ArrayList<Map<String,String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		String sqlCount= "";
		Connection con =  db.getConnection();
		PreparedStatement ps = null;
		if(para == null){
		whereSql = " pid = 0";
		 if(name!= null && !name.equals("")){
			 whereSql +=" and name like '%"+name+"%'" ;
		 }
		}else{
			whereSql = " pid ="+para;	
		}
		try {
			String sql= 
				   "select * from "+ 
				    "("+ 
				    "select id,name,pid, "+ 
				    "(row_number() over(ORDER BY id )) rb "+ 
				    "from dictionary where "+whereSql+ 
				    ") tt " + 
				    "where tt.rb between "+ begin+" and "+end;   
             sqlCount = "select id,name,pid "+
			    "from dictionary where "+whereSql; 
            ps = con.prepareStatement(sql);
		    rs = ps.executeQuery();
			try {
				while (rs.next()) {
					Map<String,String> mapdata = new HashMap<String,String>();
					mapdata.put("id", rs.getString(1));
					mapdata.put("name", rs.getString(2));
					mapdata.put("pid", rs.getString(3));
					datalist.add(mapdata);
				}
				int index = db.GetDataCount(sqlCount);
				datas = JSONArray.fromObject(datalist);
				json.put("success", true);
				json.put("datas", datas);
				json.put("results", index);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            { 
            	rs.close();
            	ps.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}	
	
	//增加字典
	public void addDictionary(){ 
		ConnDB db = new ConnDB();
		PreparedStatement ps = null;
		String  name = ServletActionContext.getRequest().getParameter("name");
		String  pid = ServletActionContext.getRequest().getParameter("pid");
		Connection con = db.getConnection();
		try {
			String sql = "insert into dictionary(name,pid) values(?,?)";
			try {
				ps = con.prepareStatement(sql);
				int index = 0;
				ps.setString(++index, name);
				ps.setString(++index, pid);
				int rs = ps.executeUpdate();
				if(rs > 0){
			//		System.out.println("保存成功!!!");
				}
				JSONObject json = new JSONObject();
				json.put("success", true);
				HttpServletResponse response = ServletActionContext.getResponse();     
				response.setCharacterEncoding("UTF-8");  
				PrintWriter out = response.getWriter();
				out.print(json);
				out.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally
        {
            try
            { 
            	ps.close();
            	con.close();
            }
            catch(Exception ex1)
            {
                ex1.printStackTrace();
            }
       }
	}
	
	  public void updateDictionary(){
			ConnDB db = new ConnDB();
			PreparedStatement ps = null;
			String  name = ServletActionContext.getRequest().getParameter("name");
			String  id = ServletActionContext.getRequest().getParameter("id");
			Connection con = db.getConnection();
			try {
				String sql = "update dictionary set name = ? where id = ?";
				try {
					ps = con.prepareStatement(sql);
					int index = 0;
					ps.setString(++index, name);
					ps.setString(++index, id);
					int rs = ps.executeUpdate();
					if(rs > 0){
				//		System.out.println("保存成功!!!");
					}
					JSONObject json = new JSONObject();
					json.put("success", true);
					HttpServletResponse response = ServletActionContext.getResponse();     
					response.setCharacterEncoding("UTF-8");  
					PrintWriter out = response.getWriter();
					out.print(json);
					out.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally
	        {
	            try
	            { 
	            	ps.close();
	            	con.close();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	       }
		  }
	  
	  //删除 字典表数据
	  public void deleteDictionary(){
			ConnDB db = new ConnDB();
			Statement ps = null;
			HttpServletRequest req = ServletActionContext.getRequest();
			String  id = req.getParameter("id");
			String  layer = req.getParameter("layer");
			String sqld = "";
		    id = id.substring(0,id.length()-1);
			Connection con = db.getConnection();
			try {
				con.setAutoCommit(false);
				ps=con.createStatement();
				 
			    boolean bool=false; 
				if(layer != null && layer.equals("0")){
					sqld = "delete dictionary  where pid in( "+id+" )";
					ps.execute(sqld);
				}
				String sql = "delete dictionary  where id in( "+id+" )";
				bool =ps.execute(sql);
					ps = con.createStatement();
				    if(bool){
				//		System.out.println("保存成功!!!");
					}
					JSONObject json = new JSONObject();
					json.put("success", true);
					HttpServletResponse response = ServletActionContext.getResponse();     
					response.setCharacterEncoding("UTF-8");  
					PrintWriter out = response.getWriter();
					out.print(json);
					out.close();
			} catch (Exception e) {
				e.printStackTrace();
				try {
					con.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}finally
	        {
	            try
	            { 
	            	con.commit();
	            	ps.close();
	            	con.close();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	       }
		  }
	  
	  //获取 字典
		public void getDictionaryTree(){ 
			HeraldAction her = new HeraldAction();
			HttpServletRequest req = ServletActionContext.getRequest();
			String  alarmId = req.getParameter("alarmId");
			List<String> list = her.getHeralSelected(alarmId);
			String selectedId = "";
			String[] seleId =null;
			if(list != null && list.size()>0){
				selectedId = list.get(0);
				seleId = selectedId.split(",");
			}
			String target = "";
			ConnDB db = new ConnDB();
			ResultSet rs = null;
			JSONArray datas = new JSONArray();
			String id = null;
			//主树
			if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
	             id = "0";
			}
			else
			{
				id = ServletActionContext.getRequest().getParameter("parentId");
			}
			Connection con =  db.getConnection();
			PreparedStatement ps = null;
			try {
				String sql = "select id,name,pid from dictionary where pid = "+id+" order by id asc";
				 
				ps = con.prepareStatement(sql);
			    rs = ps.executeQuery();
				try {
					while (rs.next()) {
						JSONObject allAlbum = new JSONObject();
						 String iid = rs.getString(1);  
						allAlbum.put("id", Integer.parseInt(rs.getString(1)));
						allAlbum.put("text", rs.getString(2));
						for(int i =0 ;i<seleId.length;i++){
							target =  seleId[i];
							if(iid.equals(target)){
								allAlbum.put("checked", true);
								break;
							}else{
								allAlbum.put("checked", false);
							}
							 
						}
						//如果是0就是父节点
				//		if("0".equals(rs.getString(3))){
				//			allAlbum.put("leaf", false);
					    //如果是1就是子节点
				//		}else if("1".equals(rs.getString(3))){
							allAlbum.put("leaf", true);
				//		}
						allAlbum.put("pid", Integer.parseInt(rs.getString(3)));
						datas.add(allAlbum);
					}
					HttpServletResponse response = ServletActionContext.getResponse();     
					response.setCharacterEncoding("UTF-8");  
					PrintWriter out = response.getWriter();
					out.print(datas.toString());
					out.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally
	        {
	            try
	            {
	            	ps.close();
	            	rs.close();
	            	con.close();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	       }
		}
		
		//获取 字典
		public void getDictionaryTreehadle(){ 
			HeraldAction her = new HeraldAction();
			HttpServletRequest req = ServletActionContext.getRequest();
			String  alarmId = req.getParameter("alarmId");
			List<String> list = her.getHandleSelected(alarmId);
			String selectedId = "";
			String[] seleId =null;
			if(list != null && list.size()>0){
				selectedId = list.get(0);
				seleId = selectedId.split(",");
			}
			String target = "";
			ConnDB db = new ConnDB();
			ResultSet rs = null;
			JSONArray datas = new JSONArray();
			String id = null;
			//主树
			if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
	             id = "0";
			}
			else
			{
				id = ServletActionContext.getRequest().getParameter("parentId");
			}
			Connection con =  db.getConnection();
			PreparedStatement ps = null;
			try {
				String sql = "select id,name,pid from dictionary where pid = "+id+" order by id asc";
				 
				ps = con.prepareStatement(sql);
			    rs = ps.executeQuery();
				try {
					while (rs.next()) {
						JSONObject allAlbum = new JSONObject();
						 String iid = rs.getString(1);  
						allAlbum.put("id", Integer.parseInt(rs.getString(1)));
						allAlbum.put("text", rs.getString(2));
						for(int i =0 ;i<seleId.length;i++){
							target =  seleId[i];
							if(iid.equals(target)){
								allAlbum.put("checked", true);
								break;
							}else{
								allAlbum.put("checked", false);
							}
						}
						//如果是0就是父节点
				//		if("0".equals(rs.getString(3))){
				//			allAlbum.put("leaf", false);
					    //如果是1就是子节点
				//		}else if("1".equals(rs.getString(3))){
							allAlbum.put("leaf", true);
				//		}
						allAlbum.put("pid", Integer.parseInt(rs.getString(3)));
						datas.add(allAlbum);
					}
					HttpServletResponse response = ServletActionContext.getResponse();     
					response.setCharacterEncoding("UTF-8");  
					PrintWriter out = response.getWriter();
					out.print(datas.toString());
					out.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally
	        {
	            try
	            {
	            	ps.close();
	            	rs.close();
	            	con.close();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	       }
		}
		
		  //获取 字典
		public void getDictionaryTreeNew(){ 
			ConnDB db = new ConnDB();
			ResultSet rs = null;
			JSONArray datas = new JSONArray();
			String id = null;
			//主树
			if("-1".equals(ServletActionContext.getRequest().getParameter("parentId"))){
	             id = "0";
			}
			else
			{
				id = ServletActionContext.getRequest().getParameter("parentId");
			}
			Connection con =  db.getConnection();
			PreparedStatement ps = null;
			try {
				String sql = "select id,name,pid from dictionary where pid = "+id+" order by id asc";
			 
				ps = con.prepareStatement(sql);
			    rs = ps.executeQuery();
				try {
					while (rs.next()) {
						JSONObject allAlbum = new JSONObject();
						allAlbum.put("id", Integer.parseInt(rs.getString(1)));
						allAlbum.put("text", rs.getString(2));
						allAlbum.put("checked", false);
						//如果是0就是父节点
				//		if("0".equals(rs.getString(3))){
				//			allAlbum.put("leaf", false);
					    //如果是1就是子节点
				//		}else if("1".equals(rs.getString(3))){
							allAlbum.put("leaf", true);
				//		}
						allAlbum.put("pid", Integer.parseInt(rs.getString(3)));
						datas.add(allAlbum);
					}
					HttpServletResponse response = ServletActionContext.getResponse();     
					response.setCharacterEncoding("UTF-8");  
					PrintWriter out = response.getWriter();
					out.print(datas.toString());
					out.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally
	        {
	            try
	            {
	            	ps.close();
	            	rs.close();
	            	con.close();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	       }
		}
		
		// 取下拉菜单
		public static String getDictionaryCombo(String dictionaryId){
			String sql ="";
			ConnDB db = new ConnDB();
			ResultSet rs = null;
			Map<String,String> allAlbum = null;
			Connection con =  db.getConnection();
			PreparedStatement ps = null;
			String re ="";
			try {
			   sql = "select id,name from dictionary where pid = '"+dictionaryId+"' order by id asc";
				 
				ps = con.prepareStatement(sql);
			    rs = ps.executeQuery();
			      allAlbum = new HashMap<String,String>();
					while (rs.next()) {
						allAlbum.put(rs.getString(1),rs.getString(2));
					}
				re =	ServletHelp.getArrayFromMap(allAlbum, null);
			} catch (Exception e) {
				e.printStackTrace();
			}finally
	        {
	            try
	            {
	            	rs.close();
	            	ps.close();
	            	con.close();
	            }
	            catch(Exception ex1)
	            {
	                ex1.printStackTrace();
	            }
	            return re;
	       }
		}
		
		
}
